package com.enation.app.javashop.core.wms.utils;

import com.enation.app.javashop.core.goods.model.vo.CategoryVO;
import com.enation.app.javashop.core.goods.service.CategoryManager;
import com.enation.app.javashop.core.trade.order.model.enums.ShipStatusEnum;
import com.enation.app.javashop.core.wms.model.dto.WmsOrderQueryParam;
import com.enation.app.javashop.core.wms.model.enums.WmsOrderStatusEnums;
import com.enation.app.javashop.framework.context.UserContext;
import com.enation.app.javashop.framework.util.SqlUtil;
import com.enation.app.javashop.framework.util.StringUtil;
import com.google.common.collect.Lists;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * @author JFeng
 * @date 2020/9/25 11:05
 */
public class WmsSqlBuilder {

    @Autowired
    private CategoryManager categoryManager;

    public  static List<Object>  wmsOrderPageSql(WmsOrderQueryParam param,StringBuilder sql){
        sql.append(" SELECT wo.delivery_sn,wo.delivery_date,wo.delivery_time,wo.delivery_status,wo.delivery_id,wo.sorting_batch,wo.sorting_seq,wo.order_sn," +
                " wo.order_id,wo.ship_name,wo.ship_mobile,wo.ship_addr,wo.order_type wms_order_type,wo.site_addr,wo.items_json,wo.receive_time,wo.receive_time_type," +
                "wo.order_num,wo.order_seq,wo.sorting_seq_list," +
                "cl.claim_sn claims_sn," +
                " oo.create_time,oo.order_type,oo.shipping_type,oo.order_status,oo.create_time order_create_time,oo.shipping_price shipping_price,oo.order_price order_price,oo.order_flag order_flag," +
                " ld.leader_name site_name,ld.site_name site_short_name,ld.leader_mobile site_mobile, " +
                " m.real_name distributor_name,m.mobile distributor_mobile" +
                " FROM  wms_order wo " +
                "LEFT JOIN es_order oo ON wo.order_sn=oo.sn " +
                "LEFT JOIN es_distribution_order dis on oo.order_id = dis.order_id " +
                "LEFT JOIN es_claims cl on wo.claims_id = cl.claim_id " +
                "LEFT JOIN es_member m ON dis.member_id_lv1 = m.member_id " +
                "LEFT JOIN es_leader ld ON ld.leader_id = oo.leader_id WHERE 1=1 ");

        List<Object> term = new ArrayList<>();
        sql.append(" and wo.seller_id = ? ");
        term.add( param.getSellerId() );

        // 出库单号
        if (StringUtils.isNotEmpty(param.getDeliverySn())) {
            sql.append(" and wo.delivery_sn like ? ");
            term.add("%" + param.getDeliverySn() + "%");
        }

        // 商品名称
        if (StringUtils.isNotEmpty(param.getGoodName())) {
            sql.append(" and wo.items_json like ? ");
            term.add("%" + param.getGoodName() + "%");
        }

        // 出库单号
        if (StringUtils.isNotEmpty(param.getKeyword())) {
            String keyword = param.getKeyword().trim();
            if(keyword.contains("岱山")){
                sql.append(" and wo.site_addr like ?  and wo.site_addr not like '%衢山镇%' ");
                term.add("%" + param.getKeyword() + "%");
            }
            else if(keyword.contains("衢山")){
                sql.append(" and wo.site_addr like ? ");
                term.add("%" + param.getKeyword() + "%");
            }else {
                sql.append(" and  wo.order_sn like ?  ");
                term.add("%" + param.getKeyword() + "%");
            }
        }

        // 出库状态
        if (StringUtils.isNotEmpty(param.getDeliveryStatus()) && param.getSortingBatch()==null) {
            sql.append(" and wo.delivery_status = ? ");
            term.add( param.getDeliveryStatus() );
        }

        // 订单类型
        if (StringUtils.isNotEmpty(param.getOrderType())) {
            sql.append(" and wo.order_type = ? ");
            term.add( param.getOrderType() );
        }

        // 出库单单号
        if(!CollectionUtils.isEmpty(param.getDeliverySns())){
            List<String> deliverySns = param.getDeliverySns();
            String str = SqlUtil.getInSql(deliverySns.toArray(new String[deliverySns.size()]), term);
            sql.append(" and wo.delivery_sn in ( "+str+ ") ");
            term.addAll( deliverySns );
        }

        // 出库单id
        if(!CollectionUtils.isEmpty(param.getDeliveryIds())){
            List<Integer> deliveryIds = param.getDeliveryIds();
            String str = SqlUtil.getInSql(deliveryIds.toArray(), term);
            sql.append(" and wo.delivery_id in ( "+str+ ") ");
        }



        // 订单状态
        if (StringUtils.isNotEmpty(param.getOrderStatus())) {
            sql.append(" and oo.order_status = ? ");
            term.add(param.getOrderStatus());
        }

        // 订单状态
        if (StringUtils.isNotEmpty(param.getOrdType())) {
            sql.append(" and oo.order_type = ? ");
            term.add(param.getOrdType());
        }

        // 配送方式
        if (StringUtils.isNotEmpty(param.getShippingType())) {
            sql.append(" and oo.shipping_type = ? ");
            term.add(param.getShippingType());
        }

        // 收货人
        if (StringUtils.isNotEmpty(param.getShipName())) {
            sql.append(" and wo.ship_name like ? ");
            term.add("%" + param.getShipName() + "%");
        }

        // 收货电话
        if (StringUtils.isNotEmpty(param.getShipMobile())) {
            sql.append(" and wo.ship_mobile like ? ");
            term.add("%" + param.getShipMobile() + "%");
        }

        // 站点名称
        if (StringUtils.isNotEmpty(param.getSiteName())) {
            sql.append(" and wo.site_name like ? ");
            term.add("%" + param.getSiteName() + "%");
        }

        // 站点名称
        if (StringUtils.isNotEmpty(param.getDeliveryType())) {
            sql.append(" and wo.delivery_type = ? ");
            term.add(param.getDeliveryType());
        }

        // 分拣批次
        if(param.getSortingBatch()!=null || param.getSortingSeq()!=null){
            if (param.getSortingBatch()!=null ) {
                sql.append(" and wo.sorting_batch = ? ");
                term.add(param.getSortingBatch());
            }
            if(param.getSortingSeq()!=null){
                sql.append(" and wo.sorting_seq = ? ");
                term.add(param.getSortingSeq());
            }
            sql.append(" and wo.delivery_status = 'READY_OUT' ");
        }

        // 时间区间
        if (param.getStartTime()!=null && param.getEndTime()!=null ) {
            sql.append(" and oo.create_time > ? and oo.create_time< ?");
            term.add(param.getStartTime());
            term.add(param.getEndTime());
        }

        // 配送日期
        if (param.getDeliveryDate()!=null) {
            sql.append(" and wo.delivery_date = ? ");
            term.add(param.getDeliveryDate());
        }

        // 送达时间
        if (param.getReceiveTime() != null && param.getReceiveTimeType() != null) {
            sql.append(" and wo.receive_time = ? ");
            term.add(param.getReceiveTime());
            sql.append(" and wo.receive_time_type = ? ");
            term.add(param.getReceiveTimeType());
        }

        // 订单编号 不要移动
        String orderSn = param.getOrderSn();
        if (StringUtils.isNotEmpty(param.getOrderSn())) {
            orderSn = orderSn.replace("，", ",");
            String[] orderSnOld = orderSn.split(",");
            String[] orderSnStr = new String[orderSnOld.length];
            for (int i = 0; i < orderSnOld.length; i++) {
                orderSnStr[i] = "?";
                term.add(orderSnOld[i]);
            }
            String sn = StringUtil.arrayToString(orderSnStr, ",");
            sql.append(" and (oo.sn in ("+sn+") or ");
        }
        // 不要移动
        if (StringUtils.isNotEmpty(param.getOrderSn())) {
            orderSn = orderSn.replace("，", ",");
            String[] orderSnOld = orderSn.split(",");
            String[] orderSnStr = new String[orderSnOld.length];
            for (int i = 0; i < orderSnOld.length; i++) {
                orderSnStr[i] = "?";
                term.add(orderSnOld[i]);
            }
            String sn = StringUtil.arrayToString(orderSnStr, ",");
            sql.append("  cl.claim_sn in ("+sn+") )");
        }

        // 排序
        if(StringUtils.isNotEmpty(param.getSortField())){

        }else {
            sql.append(" ORDER BY delivery_date DESC , sorting_batch ASC , sorting_seq ASC ");
        }
        return term;
    }

    public  static List<Object>  skuOrderQuery(WmsOrderQueryParam param,StringBuilder sql) {
        sql.append("SELECT wi.name,wi.sku_id,wi.ship_num,wo.sorting_batch,wo.sorting_seq,wo.order_sn ,wo.site_name , c.category_path category_path,CASE wo.order_type WHEN 'ORDER' THEN '订单' ELSE '理赔单' END as order_type " +
                " FROM wms_order_items wi " +
                "LEFT JOIN  wms_order wo  ON wo.delivery_id=wi.wms_order_id " +
                "LEFT JOIN es_goods g ON wi.goods_id=g.goods_id  " +
                "LEFT JOIN es_category c ON g.category_id=c.category_id  " +
                "WHERE 1=1 AND wi.ship_status!=?  and wo.seller_id=?  AND wo.delivery_date=? AND wo.delivery_type=? AND wo.receive_time = ? AND  wo.receive_time_type = ?  " +
                " AND ( wo.ship_addr is null or wo.ship_addr not like '%岱山%')  " +
                "ORDER BY wo.sorting_batch ,wi.name, wo.sorting_seq ;");
        List<Object> term = new ArrayList<>();
        term.add(ShipStatusEnum.SHIP_CANCEL.name());
        term.add( UserContext.getSeller().getSellerId() );
        term.add(param.getDeliveryDate());
        term.add(param.getDeliveryType());
        term.add(param.getReceiveTime());
        term.add(param.getReceiveTimeType());

        return term;
    }

    public static List<Object> skuSupplierQuery(WmsOrderQueryParam param, StringBuilder sql,Integer outOfStockFlag) {
        List<Object> term = new ArrayList<>();
        sql.append("SELECT wi.name,wi.sku_id,wi.supplier_name,SUM(wi.ship_num) goods_count,wo.delivery_date,g.store,g.pre_sort,c.name category_name,c.category_path category_path," +
                "gs.cost,gs.price " +
                "FROM  wms_order_items  wi  " +
                "LEFT JOIN  wms_order wo  ON wo.delivery_id=wi.wms_order_id  " +
                "LEFT JOIN es_goods g ON wi.goods_id=g.goods_id  " +
                "LEFT JOIN es_goods_sku gs ON wi.sku_id=gs.sku_id  " +
                "LEFT JOIN es_category c ON g.category_id=c.category_id  " +
                "WHERE wo.seller_id= ? AND wi.ship_status!=?  AND wo.receive_time = ? AND  wo.receive_time_type = ? "
               );
        term.add( UserContext.getSeller().getSellerId() );
        term.add(ShipStatusEnum.SHIP_CANCEL.name());
        term.add(param.getReceiveTime());
        term.add(param.getReceiveTimeType());
        if(outOfStockFlag != null && outOfStockFlag == 1){
            sql.append(" and wo.delivery_status in (?,?)");
            term.add(WmsOrderStatusEnums.DELAY_OUT.name());
            term.add(WmsOrderStatusEnums.OUT_SUCC.name());
        }

        if(param.getIsDaiShan()!=null && param.getIsDaiShan()){
            sql.append( " AND ((wo.ship_addr like '%岱山%' and wo.ship_addr not like '%衢山镇%') OR (wo.site_addr like '%岱山%' and wo.site_addr not like '%衢山镇%'))");
            sql.append( " GROUP BY wi.name ORDER BY c.category_id");
        }

        else if(param.getIsQuShan()!=null && param.getIsQuShan()){
            // 是衢山且是超市
            if (param.getIsQushanMarket() != null && param.getIsQushanMarket()) {
                sql.append(" AND c.category_id in (SELECT c2.category_id FROM es_category  c\n" +
                        "INNER JOIN es_category c1 ON c.category_id=c1.parent_id\n" +
                        "INNER JOIN es_category c2 ON c1.category_id=c2.parent_id\n" +
                        "WHERE c.category_id in (569,735)) ");
            }
            sql.append( " AND ((wo.ship_addr like '%衢山镇%') OR (wo.site_addr like '%衢山镇%')) ");
            sql.append( " GROUP BY wi.name ORDER BY c.category_id");

        }

        else  if(param.getIsBenDao()!=null && param.getIsBenDao()){
            sql.append( " AND ((wo.ship_addr not like '%岱山%') OR (wo.site_addr  not like '%岱山%')) ");
            sql.append( " GROUP BY wi.name ORDER BY c.category_id");
        } else{
            sql.append( " GROUP BY wi.name ORDER BY wi.supplier_name");
        }

        return term;
    }

    public static List<Object> orderSortingQuery(WmsOrderQueryParam param, StringBuilder sql) {
        sql.append("SELECT wo.order_sn, wo.sorting_batch, wo.sorting_seq,wo.remarks," +
                " CASE wo.shipping_type WHEN 'SELF' THEN wo.site_name ELSE 'C端' END as site_name , " +
                " CASE wo.order_type WHEN 'ORDER' THEN '订单' ELSE '理赔单' END as order_type ," +
                " CASE wo.order_type WHEN 'ORDER' THEN wo.order_sn ELSE cl.claim_sn END as dispatch_no " +
                " FROM wms_order wo " +
                "LEFT JOIN es_claims cl on wo.claims_id = cl.claim_id " +
                " WHERE wo.seller_id= ?  AND wo.delivery_date= ?  AND wo.delivery_type=?  AND wo.receive_time = ? AND  wo.receive_time_type = ? " +
                " AND ( wo.ship_addr is null or wo.ship_addr not like '%岱山%') " +
                " ORDER BY  sorting_batch,sorting_seq");
        List<Object> term = new ArrayList<>();
        term.add( UserContext.getSeller().getSellerId() );
        term.add(param.getDeliveryDate());
        term.add(param.getDeliveryType());
        term.add(param.getReceiveTime());
        term.add(param.getReceiveTimeType());
        return term;
    }

    public static List<Object> siteSkuQuery(WmsOrderQueryParam param, StringBuilder sql) {
        sql.append(" SELECT wo.site_name,wi.name,sum(wi.ship_num) sku_total , " +
                "CASE wo.order_type WHEN 'ORDER' THEN '订单' ELSE '理赔单' END as order_type " +
                " FROM wms_order_items wi LEFT JOIN  wms_order wo  ON wo.delivery_id=wi.wms_order_id " +
                "WHERE 1=1  AND wi.ship_status!=?   AND wo.seller_id=?  AND wo.delivery_date=? AND wo.delivery_type=?  AND wo.receive_time = ? AND  wo.receive_time_type = ? " +
                "GROUP BY  wo.site_id ,wi.sku_id " +
                "ORDER BY wo.site_id , wi.name  ");
        List<Object> term = new ArrayList<>();
        term.add(ShipStatusEnum.SHIP_CANCEL.name());
        term.add( UserContext.getSeller().getSellerId() );
        term.add(param.getDeliveryDate());
        term.add(param.getDeliveryType());
        term.add(param.getReceiveTime());
        term.add(param.getReceiveTimeType());

        return term;
    }

    public static List<Object> wmsOrderItemsSql(List<Integer> wmsOrderIds, StringBuilder sql) {
        sql.append("SELECT * FROM wms_order_items WHERE 1=1 ");
        List<Object> term = new ArrayList<>();
        String str = SqlUtil.getInSql(wmsOrderIds.toArray(), term);
        sql.append(" and wms_order_id in ( "+str+ ") ");
        return term;
    }

    public static List<Object> siteListQuery(WmsOrderQueryParam param, StringBuilder sql) {
        sql.append("SELECT leader_id,county,town,site_name,leader_name,leader_mobile,address FROM es_leader WHERE leader_id in (SELECT DISTINCT(site_id) " +
                "FROM wms_order WHERE seller_id= ?  AND delivery_date= ? AND delivery_type=?  AND receive_time = ? AND  receive_time_type = ? ) ");
        List<Object> term = new ArrayList<>();
        term.add( UserContext.getSeller().getSellerId() );
        term.add(param.getDeliveryDate());
        term.add(param.getDeliveryType());
        term.add(param.getReceiveTime());
        term.add(param.getReceiveTimeType());

        return term;
    }

    public static List<Object> siteBuyerQuery(WmsOrderQueryParam param, StringBuilder sql) {
        sql.append("SELECT wo.ship_name,wo.ship_mobile,wo.order_sn,sum(wi.ship_num)  sku_total,wo.site_name,wo.delivery_sn,wo.site_addr siteAddr, " +
                " CASE wo.order_type WHEN 'ORDER' THEN '订单' ELSE '理赔单' END as order_type , " +
                " CASE wo.order_type WHEN 'ORDER' THEN wo.order_sn ELSE cl.claim_sn END as dispatch_no " +
                " FROM wms_order_items wi " +
                "LEFT JOIN wms_order wo on wi.wms_order_id=wo.delivery_id " +
                "LEFT JOIN es_claims cl on wo.claims_id = cl.claim_id " +
                "WHERE   wi.ship_status!=? and wo.seller_id=? and   wo.delivery_date= ?  and wo.delivery_type=?  AND wo.receive_time = ? AND  wo.receive_time_type = ? " +
                "GROUP BY wi.order_sn  ORDER BY wo.site_id , wo.ship_name ");
        List<Object> term = new ArrayList<>();
        term.add(ShipStatusEnum.SHIP_CANCEL.name());
        term.add( UserContext.getSeller().getSellerId() );
        term.add(param.getDeliveryDate());
        term.add(param.getDeliveryType());
        term.add(param.getReceiveTime());
        term.add(param.getReceiveTimeType());

        return term;
    }

    public static List<Object> orderDeliveryQuery(WmsOrderQueryParam param, StringBuilder sql) {
        sql.append("SELECT wo.order_sn , " +
                "CASE wo.shipping_type WHEN 'SELF' THEN '站点自提' ELSE '快速到家' END as  ship_way, " +
                "CASE wo.shipping_type WHEN 'SELF' THEN wo.site_name ELSE wo.ship_name END as ship_name, " +
                "CASE wo.shipping_type WHEN 'SELF' THEN wo.site_mobile ELSE wo.ship_mobile END as ship_mobile," +
                "CASE wo.shipping_type WHEN 'SELF' THEN wo.site_addr ELSE wo.ship_addr END as  ship_addr , " +
                " CASE wo.order_type WHEN 'ORDER' THEN wo.order_sn ELSE cl.claim_sn END as dispatch_no " +
                "FROM wms_order wo " +
                "LEFT JOIN es_leader ld ON  wo.site_id=ld.leader_id " +
                "LEFT JOIN es_claims cl on wo.claims_id = cl.claim_id " +
                "WHERE wo.seller_id= ? AND wo.delivery_date= ?  AND wo.delivery_type= ?  AND wo.receive_time = ? AND  wo.receive_time_type = ? " +
                " AND ( wo.ship_addr is null or wo.ship_addr not like '%岱山%') " +
                "ORDER BY wo.sorting_batch, ship_name");
        List<Object> term = new ArrayList<>();
        term.add( UserContext.getSeller().getSellerId() );
        term.add(param.getDeliveryDate());
        term.add(param.getDeliveryType());
        term.add(param.getReceiveTime());
        term.add(param.getReceiveTimeType());

        return term;
    }
}
